import json

import mysql_util as sql_util

org_code = '550522922'
org_code_new = '057876111'


# mysql数据替换语句生成
def check_has_data(table_name,column_name):
    sql = f"select 1 num from dual where exists (select * from {table_name} t where {column_name} = '{org_code}')"
    rs = sql_util.select_by_sql(sql)
    rs = json.loads(rs)
    num = 0
    if len(rs) > 0:
        num = int(rs[0]['num'])
    if num > 0:
        print('-- ' + table_name)
        update_sql = f"update {table_name} set {column_name} = 'X{org_code_new}' where {column_name} = '{org_code_new}' ;"
        update_sql2 = f"update {table_name} set {column_name} = '{org_code_new}' where {column_name} = '{org_code}' ;"
        #print(update_sql)
        print(update_sql2)


def get_exclude_tables():
    exclude_tables = []
    exclude_tables.append('ads_it_dead_feed_stat_d')
    exclude_tables.append('bulletin_board_data')
    exclude_tables.append('cf_bi_farm_server')
    exclude_tables.append('ill_immunity_record')
    exclude_tables.append('ms_data_source_info')
    exclude_tables.append('pc_access_day_log')
    exclude_tables.append('pc_crystal_export_log')
    exclude_tables.append('pc_expert_push')
    exclude_tables.append('pc_farm_information')
    exclude_tables.append('pc_farmtype_history')
    exclude_tables.append('pc_index_history')
    exclude_tables.append('pc_op_test')
    exclude_tables.append('pc_record_point')
    exclude_tables.append('pc_report_event_tracking')
    exclude_tables.append('pc_sl_distince')
    exclude_tables.append('pc_ss_relation')
    exclude_tables.append('pc_stat_user_org')
    exclude_tables.append('pc_statistics_data_farm')
    exclude_tables.append('pc_statistics_event_tracking')
    exclude_tables.append('pc_swine_farm')
    exclude_tables.append('pc_swine_picture')
    exclude_tables.append('pc_transfer')
    exclude_tables.append('pc_user_feedback')
    exclude_tables.append('pig_stock')
    exclude_tables.append('report_cost_close_fat')
    exclude_tables.append('report_farm_information')
    exclude_tables.append('report_iot_statistics')
    exclude_tables.append('stat_farm_input_order')
    exclude_tables.append('stat_farm_users')
    exclude_tables.append('stat_svc_users')
    exclude_tables.append('stat_utm_event_base')
    exclude_tables.append('vw_pc_user_organization_org')
    exclude_tables.append('warn_center_order_user')
    exclude_tables.append('warn_center_work_order')
    exclude_tables.append('weigh_farm_info_collect')
    exclude_tables.append('zcn_radar_data_timely')
    exclude_tables.append('zcn_radar_data_timely_user')
    exclude_tables.append('weigh_usage_report_month_sale_farm')
    exclude_tables.append('radar_data_log')
    exclude_tables.append('pc_production_index')
    exclude_tables.append('weigh_usage_report_month_sale')
    exclude_tables.append('')
    exclude_tables.append('')
    exclude_tables.append('')
    return exclude_tables


if __name__ == '__main__':
    sql = "SELECT distinct TABLE_NAME,t.COLUMN_NAME FROM information_schema.COLUMNS t WHERE COLUMN_NAME like  '%org_code%' and t.table_NAME not like '%_log%' and t.table_NAME not like '%_test%'"
    rs = sql_util.select_by_sql(sql)
    rs = json.loads(rs)
    exclude_tables = get_exclude_tables()

    print(exclude_tables)
    print('START TRANSACTION;')
    for d in rs:
        # print(d['TABLE_NAME'])
        table_name = d['TABLE_NAME']
        if table_name not in exclude_tables:
            # print(table_name)
            column_name = d['COLUMN_NAME']
            check_has_data(table_name,column_name)
    print('COMMIT;')
    print('ROLLBACK;')
